# 职位界面的路由
from flask import Blueprint,jsonify,request
from flask import current_app,render_template
from sqlalchemy import  *
from job.models import Job,CateWC
from job import db
import sqlalchemy.sql.expression as expr


job = Blueprint("job2",__name__)

def get_FilterZiduan(ziduan):
    if ziduan in ["民营公司","外资","上市公司","合资","国企","创业公司","事业单位"]:
        return Job.ctype
    elif ziduan in ["50-150人","150-500人","少于50人","1000-5000人","500-1000人","10000人以上","5000-10000人"]:
        return Job.cnum
    elif ziduan in ["大专","本科","高中/中技/中专","硕士","初中及以下","博士"]:
        return Job.edu
    elif ziduan in ["1-2年","3-4年","5-9年","无需经验","10年以上"]:
        return Job.exp
    elif ziduan in ["上海", "广州", "深圳", "北京", "杭州", "成都", "南京", "苏州", "武汉", "东莞", "西安", "长沙", "宁波", "无锡", "合肥", "重庆",
                        "佛山", "昆山", "郑州", "常州", "青岛", "珠海", "大连", "福州", "昆明", "济南", "惠州", "天津", "沈阳", "厦门", "南昌", "哈尔滨",
                        "中山", "嘉兴", "南通", "长春", "南宁", "温州", "徐州", "石家庄", "芜湖", "常熟", "江门", "扬州", "绍兴", "盐城", "贵阳", "湖州",
                        "金华", "泉州", "镇江", "张家港", "烟台", "泰州", "台州", "绵阳", "太仓", "淮安", "清远", "肇庆", "咸阳"]:
        return Job.city

@job.route("/")
def index():
    # print("打开了岗位页")
    cate2 = '数据分析师'
    if  request.args.get("cate2") is not None:
        # print("wogiao",request.args.get("cate2"))
        cate2 = request.args.get("cate2")
    # num_count=Job.query.filter(Job.cate2==cate2).with_entities(func.sum(Job.num)).first()
    # print("giaogiao",num_count)
    # num_count=str(num_count).replace("'),)","").replace("(Decimal('","")
    return render_template("job.html",num_count=0,work_name=cate2)


@job.route("/jobsum",methods=['POST'])
def jobsum():
    cate2 = request.form['cate2']
    try:
        postdata2 = request.form['data2']
        num_count = Job.query.filter(Job.cate2 == cate2,get_FilterZiduan(postdata2) == postdata2).with_entities(func.sum(Job.num).cast(Integer)).scalar()
    except Exception:
        num_count=Job.query.filter(Job.cate2==cate2).with_entities(  func.sum(Job.num).cast(Integer)).scalar()
    print(num_count)
    return jsonify({
        'jobsum':num_count
    })

#  工资区间分布 薪资水平
@job.route("/avg_salary", methods=['POST','GET'])
def avg_salary():
    postdata = request.form['cate2']
    try:
        postdata2 = request.form['data2']
        print(postdata2+"我进来了")
        print(str(get_FilterZiduan(postdata2)).replace("Job.",""))
        data = db.session.execute(
            'select salary,sum(t1.num)/1000 from(select if(salary<4000,"4k-",if(salary<6000,"4-6k",if(salary<8000,"6-8k",if(salary<10000,"8-10k",if(salary<15000,"10-15k",if(salary<25000,"15-25k",if(salary>=25000,"25k+","其他"))))))) salary,sum(num) num from job where cate2=\'' + postdata + '\' and '+str(get_FilterZiduan(postdata2)).replace("Job.","")+'=\''+postdata2+'\' group by salary)t1 group by salary').fetchall()
        print(data)
    except Exception:
        data = db.session.execute(
            'select salary,sum(t1.num)/1000 from(select if(salary<4000,"4k-",if(salary<6000,"4-6k",if(salary<8000,"6-8k",if(salary<10000,"8-10k",if(salary<15000,"10-15k",if(salary<25000,"15-25k",if(salary>=25000,"25k+","其他"))))))) salary,sum(num) num from job where cate2=\'' + postdata + '\' group by salary)t1 group by salary').fetchall()
        print(data)
    salary = ['4k-', '4-6k', '6-8k', '8-10k', '10-15k', '15-25k', '25k+']
    each_salaryCount=[]
    for s in salary:
        # print("giaogiao",data)
        for i in data:
            if i[0]==s:
                each_salaryCount.append(float(i[1]))
                data.pop(data.index(i))

    return jsonify({
        'A': salary,
        'B': each_salaryCount
    })

#  岗位学历要求占比
@job.route("/edu_requried", methods=['POST'])
def edu_requried():
    postdata = request.form['cate2']
    try:
        postdata2 = request.form['data2']
        result = Job.query.filter(Job.cate2 == postdata,get_FilterZiduan(postdata2) == postdata2).with_entities(Job.edu, func.sum(Job.num).cast(Integer).label("each_eduCount")).group_by(Job.edu).order_by(desc("each_eduCount")).all()
    except Exception:
        result = Job.query.filter(Job.cate2 == postdata).with_entities(Job.edu, func.sum(Job.num).cast(Integer).label("each_eduCount")).group_by(Job.edu).order_by(desc("each_eduCount")).all()
    # print("edu_requried_hhhh", postdata)
    edu = []
    each_eduCount = []
    for item in result:
        # print(item)
        edu.append(item[0])
        tmp={"value": item[1], "name":item[0]}
        each_eduCount.append(tmp)
    # print(each_eduCount)
    return jsonify({
        'A': edu,
        'B': each_eduCount
    })

#  岗位经验要求占比
@job.route("/exp_requried", methods=['POST'])
def exp_requried():
    postdata = request.form['cate2']
    try:
        postdata2 = request.form['data2']
        result = Job.query.filter(Job.cate2 == postdata,get_FilterZiduan(postdata2) == postdata2).with_entities(Job.exp,func.sum(Job.num).cast(Integer).label("each_expCount")).group_by(Job.exp).order_by(desc("each_expCount")).all()
    except Exception:
        result = Job.query.filter(Job.cate2 == postdata).with_entities(Job.exp,func.sum(Job.num).cast(Integer).label("each_expCount")).group_by(Job.exp).order_by(desc("each_expCount")).all()
    exp = ['无需经验', '1-2年', '3-4年', '5-9年', '10年以上']
    each_expCount = []
    for e in exp:
        for item in result:
            if str(item[0]) == e:
                each_expCount.append(item[1])
                result.pop(result.index(item))
    # print(each_eduCount)
    return jsonify({
        'A': exp,
        'B': each_expCount
    })

#  公司规模对比
@job.route("/company_workerNum", methods=['POST'])
def company_workerNum():
    postdata = request.form['cate2']
    try:
        postdata2 = request.form['data2']
        result = Job.query.filter(Job.cate2 == postdata,get_FilterZiduan(postdata2) == postdata2).with_entities(Job.cnum,func.count(Job.cnum).label("each_cnumCount")).group_by(Job.cnum).order_by(desc("each_cnumCount")).all()
    except Exception:
        result = Job.query.filter(Job.cate2 == postdata).with_entities(Job.cnum,func.count(Job.cnum).label("each_cnumCount")).group_by(Job.cnum).order_by(desc("each_cnumCount")).all()
    cnum_type_list=["少于50人","50-150人","150-500人","500-1000人","1000-5000人","5000-10000人","10000人以上"]
    cnum = []
    each_cnumCount = []
    max_CountNum=result[0][1]
    for cunm_type in cnum_type_list:
        for item in result:
            if str(item[0])==cunm_type:
                tmp = {"name": cunm_type, "max": max_CountNum}
                each_cnumCount.append(item[1])
                cnum.append(tmp)
                result.pop(result.index(item))
    # print(each_eduCount)
    return jsonify({
        'A': cnum,
        'B': each_cnumCount
    })

#  企业性质
@job.route("/company_type", methods=['POST'])
def company_type():
    postdata = request.form['cate2']
    try:
        postdata2 = request.form['data2']
        result = Job.query.filter(Job.cate2 == postdata,get_FilterZiduan(postdata2) == postdata2).with_entities(Job.ctype, func.sum(Job.num).cast(Integer).label("each_ctypeCount")).group_by(Job.ctype).order_by(desc("each_ctypeCount")).distinct().all()
    except Exception:
        result = Job.query.filter(Job.cate2 == postdata).with_entities(Job.ctype, func.sum(Job.num).cast(Integer).label("each_ctypeCount")).group_by(Job.ctype).order_by(desc("each_ctypeCount")).distinct().all()
    ctype = []
    each_ctypeCount = []
    small_num=0
    jsq=0
    for item in result:
        jsq+=1
        if jsq >5:
            small_num+=item[1]
            if jsq == len(result) - 1:
                ctype.append('其他')
                each_ctypeCount.append({ "value":small_num , "name": '其他'})
            continue
        # print(item)
        tmp={ "value":item[1] , "name": item[0]}
        ctype.append(item[0])
        each_ctypeCount.append( tmp)
    # print(each_eduCount)
    return jsonify({
        'A': ctype,
        'B': each_ctypeCount
    })


#  毕业月薪走势
@job.route("/educated_salary", methods=['POST'])
def educated_salary():
    postdata = request.form['cate2']
    try:
        postdata2 = request.form['data2']
        result = Job.query.filter(Job.cate2 == postdata,get_FilterZiduan(postdata2) == postdata2).with_entities(Job.exp,func.avg(Job.salary).label("avgSalary_ByExp")).group_by(Job.exp).order_by(asc("avgSalary_ByExp")).all()
    except Exception:
        result = Job.query.filter(Job.cate2 == postdata).with_entities(Job.exp,func.avg(Job.salary).cast(DECIMAL).label("avgSalary_ByExp")).group_by(Job.exp).order_by(asc("avgSalary_ByExp")).all()
    exp = ['无需经验','1-2年','3-4年','5-9年','10年以上']
    salary = []
    for e in exp:
        for item in result:
            # print(item)
            if str(item[0])==e:
                salary.append(str(item[1]))
                result.pop(result.index(item))
    salary = [round(float(a)/1000,1) for a in salary]
    return jsonify({
        'A': exp,
        'B': salary
    })

#  岗位需求量前十城市
@job.route("/worderCity_needed", methods=['POST'])
def worderCity_needed():
    postdata = request.form['cate2']
    try:
        postdata2 = request.form['data2']
        result = Job.query.filter(Job.cate2 == postdata,get_FilterZiduan(postdata2) == postdata2).with_entities(Job.city,func.sum(Job.num).cast(Integer).label("worker_needed")).group_by(Job.city).order_by(desc("worker_needed")).limit(10).all()
    except Exception:
        result = Job.query.filter(Job.cate2 == postdata).with_entities(Job.city,func.sum(Job.num).cast(Integer).label("worker_needed")).group_by(Job.city).order_by(desc("worker_needed")).limit(10).all()
    city = []
    worder_num = []
    for item in result:
        # print(item)
        city.append(item[0])
        worder_num.append( item[1])
    return jsonify({
        'A': city,
        'B': worder_num
    })

#  技能词云
@job.route("/skill_wordcloud", methods=['POST'])
def skill_wordcloud():
    postdata = request.form['cate2']
    result = CateWC.query.filter(CateWC.cate2 == postdata).with_entities(CateWC.word,CateWC.count).order_by(desc(CateWC.count))
    result = result.limit(100).all()
    word_cloud_data = []
    for item in result:

        tmp={ 'name':  item[0], 'value':  item[1]}
        word_cloud_data.append( tmp)
    # print(each_eduCount)
    return jsonify({
        'A': word_cloud_data,
        'B':"nothing,just occpy a seat"
    })

